# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
import re
import csv
import urllib.request
import mysql.connector
from mysql.connector import Error
from itemadapter import ItemAdapter


class DangdangPipeline:
    def open_spider(self,spider):
        self.fp = open('当当网.csv', 'w', encoding='utf-8', newline='')
        self.writer = csv.writer(self.fp)
        self.writer.writerow(['书名', '图片', '作者', '出版社', '原价', '折扣价', '描述', '详情页'])


    def process_item(self, item, spider):
        row = [item['book_name'],item['bookimg_link'],item['book_author'],item['press'],item['original_price'],item['discount_price'],item['describe'],item['book_link']]
        self.writer.writerow(row)
        return item

    def close_spider(self,spider):
        self.fp.close()

class MySQLPipeline:
    def __init__(self):
        self.connection = None
        self.cursor = None

    def open_spider(self, spider):
        try:
            self.connection = mysql.connector.connect(
                host='localhost',
                user='root',
                password='123456',
                database='books',
                charset='utf8'
            )
            self.cursor = self.connection.cursor()
        except Error as e:
            print(f"Error while connecting to MySQL:{e}")
    def close_spider(self, spider):
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()
    def process_item(self, item, spider):
        try:
            insert_query = """
            insert into booksinfo (book_name,book_author,press,original_price,discount_price,book_describe,book_link) values (%s,%s,%s,%s,%s,%s,%s)
            """
            val = (
                item['book_name'],
                item['book_author'],
                item['press'],
                item['original_price_number'],
                item['discount_price_number'],
                item['describe'],
                item['book_link']
            )
            self.cursor.execute(insert_query, val)
            self.connection.commit()
        except Error as e:
            print(e)

class DangDangDownLoadImg:
    def process_item(self, item, spider):
        url = 'https:' + item.get('bookimg_link')
        # 去除特殊字符
        title = re.findall('[\u4e00-\u9fff]+', item['book_name'])
        title = ''.join(title)
        filename = f'./bookImgs/{title}.jpg'
        urllib.request.urlretrieve(url=url, filename=filename)
